﻿--- NOTE ---
--- MỖI THÁNG CẦN UPDATE ---
--- 1. Ngày đầu tháng
--- 2. Bảng chuyển DAO Thẻ các tháng trước, bảng CIF QW, CIF NGoại lệ các tháng trước
--- 3. Cột tháng trong bảng KPIs Target
--- 4. Sửa công thức trong cách lấy KHHĐ tăng thêm
--- 5. Bảng Dư nợ tháng T-3

DECLARE @DATE_NOW NVARCHAR(8) =(SELECT MAX(CONVERT(NVARCHAR(8), BUSINESS_DATE,112)) FROM  M74.BICDATA.DBO.BALANCE_LIVE)
PRINT (@DATE_NOW)
DECLARE @DATE_NOW_1 NVARCHAR(10) = convert(nvarchar(10),(select convert(date, @DATE_NOW)),120)
PRINT (@DATE_NOW_1)
DECLARE @STARTOFMONTH NVARCHAR (20)= CONVERT (NVARCHAR(8),(DATEADD(M,DATEDIFF(MONTH, 0, CONVERT(DATE,@DATE_NOW)),0)),112)
PRINT @STARTOFMONTH
DECLARE @STARTOFMONTH_1 NVARCHAR (20)= convert(nvarchar(10),(select convert(date, @STARTOFMONTH)),120)
PRINT @STARTOFMONTH_1
DECLARE @ENDMONTH_LAST_3 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),120)
PRINT @ENDMONTH_LAST_3
DECLARE @ENDMONTH_LAST_3_1 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),112)
PRINT @ENDMONTH_LAST_3_1
DECLARE @YEARMONTH_LAST_MONTH VARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(D, -1,@STARTOFMONTH),112)
PRINT @YEARMONTH_LAST_MONTH

exec('
select * into SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+' 
from M74.BICDATA.DBO.LNTB_DISBURSEMENT where SEGMENT =''SMES''

select * into  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.CARD_LIVE where business_date  ='''+@DATE_NOW_1+'''
and (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'' or CARD_NUMBER LIKE ''522384%'') 

select * into  SME2017DAILY.DBO.BALANCE_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.BALANCE_LIVE where business_date  ='''+@DATE_NOW_1+'''
and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))

select * into  SME2017DAILY.DBO.FINNONE_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.FINNONE_LIVE where business_date  ='''+@DATE_NOW_1+''' 
and BI_SEGMENT in (''CORPORATE'') or BI_SEGMENT is null
')

print('--- 1 - Lấy các bảng đầu vào và update thông tin---
--- 1.1 --- Lấy bảng Khách hàng')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''VPB_CUSTOMER_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'

select * into SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
from M74.BICDATA.DBO.CUSTOMER where SEGMENT not in (''KHCN'',''HH'')
')

print('--- 1.2 --- Lấy bảng KHHĐ (sửa tên bảng, nội dung)')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KHHD_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.KHHD_'+@DATE_NOW+'

select * into SME2017DAILY.DBO.KHHD_'+@DATE_NOW+'
from server12.tranglt9.dbo.active_customer_daily_report
where report_date = ''2017-05-15'' and STATUS = ''ACTIVE''')

print('--- 1.3 --- Lấy bảng Dư nợ, Huy động Bình quân ---')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''balance'+@DATE_NOW+'_sme'')
        DROP TABLE SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme

select * into SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
from M74.SME.[dbo].[DLYTB_AVG_BAL] where BUSINESS_DATE = '''+@DATE_NOW_1+'''

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
add PRODUCT_DETAIL_2 NVARCHAR(255),
SECTOR nvarchar(40),
LOAI_THE nvarchar (40),
SOURCE_ID nvarchar(200),
ISSUE_DATE datetime ')

exec('
UPDATE a
SET a.PRODUCT_DETAIL_2 = ''6.3. Unsecured Overdraft''
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A 
inner join (SELECT * FROM  SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+' 
				WHERE (OVERDRAFT_COLLATERAL = ''N'' OR OVERDRAFT_COLLATERAL IS NULL)) AS b 
on A.ACCTNO collate database_default = B.ACCTNO
WHERE  A.PRODUCT_NAME = ''6. Overdraft'' 

UPDATE a 
SET a.SECTOR = B.SECTOR
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A
inner join SME2017DAILY.DBO.BALANCE_LIVE_'+@DATE_NOW+' AS b 
on A.ACCTNO collate database_default = B.ACCTNO

UPDATE a 
	SET a.ISSUE_DATE = B.VALUE_DATE
	from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A
	inner join SME2017DAILY.DBO.LNTB_MASTER B
	on A.ACCTNO collate database_default  = B.ACCTNO 

UPDATE a
	SET a.ISSUE_DATE = B.ISSUE_DATE
	from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A
	inner join SME2017DAILY.DBO.DPTB_MASTER B
	on A.ACCTNO collate database_default  = B.ACCTNO 

Update a
Set a.APP_REJ_DATE = B.APP_REJ_DATE
From SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A,  SME2017DAILY.DBO.FINNONE_LIVE_'+@DATE_NOW+' B 
where A.RRT_ID COLLATE DATABASE_DEFAULT  = B.APP_ID_C and B.business_date ='''+@DATE_NOW_1+''' and BI_APPSSTATUS = ''APPROVED''

Update a
Set a.APP_REJ_DATE = B.DIST_DATE 
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A ,  SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+'  AS b 
WHERE A.ACCTNO collate database_default = B.ACCTNO
AND A.APP_REJ_DATE is null

select *, case when  limit_ref like ''%05%'' then ''CTSDB''
			when limit_ref like ''%06%'' then ''KTSDB'' end as loai_the_m16
into #loai_the 
from M16.[VPB_WHR2].[dbo].[CARD_ISSUE_T24] 
where limit_ref like ''%06%'' or limit_ref like ''%05%''

update a
set a.SOURCE_ID = B.SOURCE_ID 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A,  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+'  B
where A.ACCTNO = convert(nvarchar,B.ACNT_CONTRACT_ID,100) and B.BUSINESS_DATE = '''+@DATE_NOW_1+'''

update a
set a.LOAI_THE = b.loai_the_m16
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme a
inner join #loai_the b
on a.SOURCE_ID = b.CARD_ID
')

print('--- 1.4 --- Lấy bảng CIF chuyển BAD BANK trong tháng')
exec('
--- Nếu chuyển vào rồi chuyển ra vẫn bị tính thì chạy code như sau
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''BADBANK_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+'

SELECT distinct CUSTOMER_ID CIF, DATE_GBBB INTO SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+'
FROM M16.[VPB_G3B].[DBO].[TBL_G3B_T24CRD] 
			WHERE BUSINESS_DATE between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''		
				and cif is not null	
				AND BRANCH_CODE_GBBB = ''VN9990001''
				AND DATE_GBBB IS NOT NULL and DATE_GBBB between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''
')

print('--- 1.5 --- Lấy bảng LD chuyển BAD BANK trong tháng')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''BALANCE_BADBANK_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'

select a.*, ''YES'' NOXAU 
into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
from M74.sme.dbo.incentive'+@DATE_NOW+' a
inner join SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' b
on a.CIF = b.CIF
where NHOMNOMOI in (''3'',''4'',''5'')

insert into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' (ACCTNO, BAL_QD, CIF, DAO, NOXAU)
select a.ACNT_CONTRACT_ID, case when a.BALANCE >= 0 then 0 else -a.BALANCE end, a.CONTRACT_CIF, a.T24_DAO,''YES''
from  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+'a
inner join SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' b
on a.CONTRACT_CIF = b.CIF
where business_date ='''+@DATE_NOW_1+'''

alter table SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
add DAO_CIF nvarchar (50) 

update SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' 
SET DAO_CIF = B.DAO
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  A 
	inner join SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	on A.CIF collate database_default = B.RECID
')

print('--- 1.6 --- Lấy bảng CARD')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CARD_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.CARD_'+@DATE_NOW+'

select *  into SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
from  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+' 
where  BUSINESS_DATE ='''+@DATE_NOW_1+''' AND (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'' or CARD_NUMBER LIKE ''522384%'')

update SME2017DAILY.DBO.CARD_'+@DATE_NOW+' set T24_DAO = B.NEW_DAO
from SME2017DAILY.DBO.CARD_'+@DATE_NOW+' A
inner join SME2017.DBO.CHUYEN_DAO_THE_'+@YEARMONTH_LAST_MONTH+' B
on A.SOURCE_ID = B.SOURCE_ID
')

print('--- 1.7 --- Update lại bảng KPIs Target')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KPI_TARGET_201705_CHANGE'')
        DROP TABLE SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE

select * into SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE from SME2017.DBO.KPI_TARGET_'+@YEARMONTH_LAST_MONTH+'

update SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE
set May2017 =  b.kpiS_may2017
from SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE A, SME2017.DBO.KPIs_201705 B
where A.DAo = B.DAO and A.CODE = B.CODE and B.Branch_code = A.Branch_code
')

print('--- 1.8 --- Update lại KH chuyển giao trong tháng')
exec('
---drop table #CHUYEN_DAOCIF_201705
select RECID, DAO as OLD_DAO into #CHUYEN_DAOCIF_201705 from SME2017.DBO.VPB_CUSTOMER_20170430

alter table #CHUYEN_DAOCIF_201705
add NEW_DAO nvarchar (50)

update #CHUYEN_DAOCIF_201705
set NEW_DAO = B.DAO 
from #CHUYEN_DAOCIF_201705 A, SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
where A.RECID COLLATE DATABASE_DEFAULT = B.RECID

---drop table #tang_KHHD 
select NEW_DAO, count(RECID) CIF into #tang_KHHD
from #CHUYEN_DAOCIF_201705
where OLD_DAO <> NEW_DAO
AND RECID in (select CIF from SME2017.DBO.KHHD_201704)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in   (select channel from channel_off) or channel is null)
	AND SEGMENT=''SMES'' )
group by NEW_DAO 

---drop table #giam_KHHD 
select OLD_DAO, count(RECID) CIF into #giam_KHHD
from #CHUYEN_DAOCIF_201705
where OLD_DAO <> NEW_DAO
and RECID in (select CIF from SME2017.DBO.KHHD_201704)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in  (select channel from channel_off) or channel is null) AND SEGMENT=''SMES'' )
group by OLD_DAO

update SME2017.DBO.KPIs_201705
set ban_giao_201705 = B.CIF
from SME2017.DBO.KPIs_201705 a, #giam_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.OLD_DAO and a.code = ''7''

update SME2017.DBO.KPIs_201705
set nhan_ban_giao_201705 = B.CIF
from SME2017.DBO.KPIs_201705 a, #tang_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.NEW_DAO and a.code = ''7''
')

print('--- 2. --- CHẠY CODE
--- 2.1 --- Lấy mẫu bảng')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''FINAL_MSBO_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'

select * into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
from SME2017.DBO.FINAL_MSBO_201702
delete SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
')

print('--- 2.2 ---lấy các mã DAO có trong KPI target và nhân sự')
exec('
insert into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' (DAO)
select distinct DAO from SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE --- cập nhật hàng tháng
where (DEPT like ''%MBO%'' or DEPT like''%SBO%'') and DAO is not null  

insert into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' (DAO)
select distinct DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+'
where (position like ''%MBO%'' or POSITION like''%SBO%'') and DAO is not null and DAO collate database_default not in 
(select convert ( nvarchar,dao,112) from SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE where dao is not null)
')

print('--- 2.3 ---Update KPI target ')
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET AVGBAL_LOAN_TARGET = MAY2017 ---- Dư nợ BQ -----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''1''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET  AVGBAL_DEPOSIT_TARGET= MAY2017 ----- Huy động BQ ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''2''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET NEWNPL_LOAN_TARGET = MAY2017 --- Nợ xấu ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''3''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET  NFI_TARGET= MAY2017 ---- Phí ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''4''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET NII_TARGET = MAY2017 ----- Lãi ---
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''5''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET AVGBAL_UPL_LOAN_TARGET = MAY2017 --- Tín chấp BQ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''6''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET CUSTOMER_TARGET = MAY2017 ---- Khách hàng hoạt động ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''7''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET CREDIT_CARD_TARGET = MAY2017 --- Thẻ ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''8''
')

print('--- 2.4 --- Thông tin về nhân viên')
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET SALE_NAME = B.SALE_NAME,
Branch_code = B.Branch_code,
position = B.position_KPI,
time_work = datediff (day, B.DATE_SALES_start, '''+@DATE_NOW+''')/30, 
REGION = b. REGION,
Staff_id = b.Staff_id,
DEPT = B.POSITION
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+' as B
where A.DAO collate DATABASE_DEFAULT = B.DAO
')
print ('xóa cif null')
exec('
delete from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is null
delete from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF is null
')

print('--- 2.5 --- Tính số thực tế
--- 2.5.1 --- Dư nợ tín chấp:')
exec(' 
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_UPL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, 
(select distinct DAO as DAO, sum(AVG_BAL)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_code =''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN'' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+') 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in (select CHUONG_TRINH_SP from CHUONG_TRINH_SP_off)
	and CHUONG_TRINH_SP not like ''%BIL%'') or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90) or Channel not in (select channel from channel_off))
	and (CHUONG_TRINH_SP in (select CHUONG_TRINH_SP from CHUONG_TRINH_SP_off) or 
	(Product_name = ''6. Overdraft'' and PRODUCT_DETAIL_2 =''6.3. Unsecured Overdraft'') or CHUONG_TRINH_SP like ''%BIL%'') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+')
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+'))
	group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--Cộng số avgbal trong thẻ nếu thẻ là KTSDB --- Nếu tạm thời chưa có bảng dư nợ thẻ thì thôi cuối tháng mới cộng
update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_UPL_LOAN_ACT = isnull(AVGBAL_UPL_LOAN_ACT,0) + isnull(B.Amt,0)
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, (select distinct DAO, sum(AVG_BAL)/1E6 as amt from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+'))
AND (LOAI_THE =''KTSDB'' or LOAI_THE is null)
AND APP =''CREDIT CARD''
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

print('--- 2.5.2 Dư nợ BQ')
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A,
(select distinct DAO as DAO, sum(AVG_BAL)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN''
	AND ((channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (select channel from channel_off) or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+')
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+') 
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+'))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- Cộng số avgbal trong thẻ
update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_LOAN_ACT = case when AVGBAL_LOAN_ACT is null then 0 else AVGBAL_LOAN_ACT END + case when 
B.Amt is null then 0 else B.Amt END
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, (select distinct DAO, sum(AVG_BAL)/1E6 as amt from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+'))
AND APP =''CREDIT CARD''
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

print('--- 3. Tính tổng huy động ')

exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_DEPOSIT_ACT =  B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A,
(select distinct DAO as DAO, sum(AVG_BAL)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
 AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
and APP = ''DEPOSIT''
and DAO is not null
and (channel <> ''SMETLS'' and channel <> ''PSASME'' or channel is null)  ---Channel CTV 
and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+')
and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+'))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

print('--- 2.5.4 Tính NPL:
---Bảng Nợ xấu')

exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''NPL_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.NPL_'+@DATE_NOW+'

Select distinct DAO into SME2017DAILY.DBO.NPL_'+@DATE_NOW+' from  SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 

alter table SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
add NO_XAU_T float,
	DU_NO_THANG_T3 float,
	TI_LE float

Update SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
set NO_XAU_T = B.AMT
FROM  SME2017DAILY.DBO.NPL_'+@DATE_NOW+' AS A, 
	(SELECT DISTINCT DAO DAO ,isnull(SUM(CASE WHEN NOXAU = ''YES'' THEN ISNULL(BAL_QD,0) END),0) AMT 
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme where (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+') 
	AND (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (select channel from channel_off) or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+')))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

Update SME2017DAILY.DBO.NPL_'+@DATE_NOW+' --- xin code update vào
set DU_NO_THANG_T3 = B.AMT
FROM  SME2017DAILY.DBO.NPL_'+@DATE_NOW+' AS A, 
	(SELECT DISTINCT DAO DAO ,ISNULL(SUM(ISNULL(BAL_QD,0)),0) AMT 
	FROM SME2017.DBO.BALANCE'+@ENDMONTH_LAST_3_1+'_SME 
	WHERE (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CIF = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2017.DBO.BADBANK_'+@ENDMONTH_LAST_3_1+')
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_201702 where CIF_DACTHU is not null) 
	AND  (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@ENDMONTH_LAST_3+''')>90 ) or Channel not in (select channel from channel_off) or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201702 where CIF is not null))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

	update SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
	set DU_NO_THANG_T3 = case when DU_NO_THANG_T3 is null then 0 else DU_NO_THANG_T3 END + case when 
	B.Amt is null then 0 else B.Amt END
	from SME2017DAILY.DBO.NPL_'+@DATE_NOW+' as A, (select distinct T24_DAO as DAO, sum( case when balance >= 0 then 0 
	when balance is null then 0 when balance < 0 then balance* -1 end)/1E6 as amt from SME2017.DBO.CARD_'+@ENDMONTH_LAST_3_1+' where BUSINESS_DATE ='''+@ENDMONTH_LAST_3+'''
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null and datediff(day, UNLOCK_DATE, '''+@ENDMONTH_LAST_3+''')<90)
	AND ( t24_DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CONTRACT_CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_201702 where CIF is not null))
	group by T24_DAO) as B
	where A.DAO collate DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.NPL_'+@DATE_NOW+' set TI_LE = ISNULL(NO_XAU_T,0)/DU_NO_THANG_T3 where DU_NO_THANG_T3 is not null and DU_NO_THANG_T3 <> 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
	SET NEWNPL_LOAN_ACT = case when B.DU_NO_THANG_T3 is null and B.NO_XAU_T > 0 then 1 else B.TI_LE end
	FROM  SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.NPL_'+@DATE_NOW+'  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO
')

print('--- 2.5.5 --- Thẻ')

exec('
--Drop table #the_mo_moi
--Drop table #the_dong

CREATE TABLE #the_mo_moi
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)

CREATE TABLE #the_dong
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)
----MỞ THẺ----
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END as AMT, ''CREDITCARD'' as TYPE into #The_tin_dung
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'') ---- Mã đầu thẻ Credit của SMEs
	AND CARD_STATUS = ''CARD OK''
	AND CONTRACT_STATUS = ''Account OK''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' ---- Không mở trước 2015, trước đó đã đc tính rồi.
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN ''20170101'' AND '''+@DATE_NOW+''' --- từ đầu 2017 đến nay
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) , T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))   END, ''CREDITCARD'' 
	FROM #The_tin_dung
	GROUP BY CONTRACT_CIF, T24_DAO
	
--- Thẻ Debit ---
INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END AMT , ''DEBITCARD''
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE CARD_NUMBER LIKE ''522384%''
	AND CARD_STATUS = ''CARD OK''
	AND CONTRACT_STATUS = ''Account OK''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' 
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN ''20170101'' AND '''+@DATE_NOW+'''---  từ đầu 2017 đến nay
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_mo_moi  
	SET DAO_CIF = A.DAO 
	FROM #the_mo_moi  AS B , SME2017.DBO.VPB_CUSTOMER_'+@ENDMONTH_LAST_3_1+' AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

--- THẺ ĐÓNG---
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END AMT, ''CREDITCARD'' TYPE into #The_tin_dung_dong
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'') ---- Mã đầu thẻ Credit của SMEs
	AND T24_DAO IS NOT NULL
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112), T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))    END AMT, ''CREDITCARD''
	FROM #The_tin_dung_dong
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Thẻ Debit ---
INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END, ''DEBITCARD'' 
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE CARD_NUMBER LIKE ''522384%''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' 
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_dong  
	SET DAO_CIF = A.DAO 
	FROM #the_dong  AS B , SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

---- Update thông tin thẻ thực tế vào bảng FINAL_MSBO---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
	SET CREDIT_CARD_ACT = isnull(B.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_mo_moi WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS B
WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  
	SET CREDIT_CARD_ACT = isnull(CREDIT_CARD_ACT,0) - isnull(C.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_dong WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS C
	WHERE  A.DAO COLLATE DATABASE_DEFAULT = C.DAO
')

print('--- 2.5.6 --- Khách hàng hoạt động tăng thêm ')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KHACH_HANG_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'

Select distinct DAO into SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 

alter table SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
add SO_LUY_KE float,
	DEC_2016 float,
	BAN_GIAO float,
	NHAN_BAN_GIAO float,
	SO_MOI float

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set Dec_2016 = CUSTOMER_ACT
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, SME2016.DBO.KPI_2017_DAU_KY B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set SO_LUY_KE = B.AMOUNT
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, (SELECT DISTINCT  DAO, COUNT (DISTINCT RECID) AMOUNT
	FROM SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_'+@DATE_NOW+') 
	AND DAO IS NOT NULL
	AND  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in (select channel from channel_off) or channel is null)
	and (SEGMENT not in (''KHCN'',''HH'') and (COMPANY_BOOK not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	GROUP BY DAO) AS B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set BAN_GIAO = isnull(B.BAN_GIAO_201701,0) + isnull(B.BAN_GIAO_201702,0) + isnull(B.BAN_GIAO_201703,0) + isnull(B.BAN_GIAO_201704,0)+ isnull(B.BAN_GIAO_201705,0)
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, SME2017.DBO.KPIs_201705 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = ''7''

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set NHAN_BAN_GIAO = isnull(B.NHAN_BAN_GIAO_201701,0)+ isnull(B.NHAN_BAN_GIAO_201702,0) + isnull(B.NHAN_BAN_GIAO_201703,0) + isnull(B.NHAN_BAN_GIAO_201704,0) + isnull(B.NHAN_BAN_GIAO_201705,0)
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' A, SME2017.DBO.KPIs_201705 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = ''7''

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set SO_MOI = isnull(SO_LUY_KE,0) - isnull(DEC_2016,0) + isnull(BAN_GIAO,0) - isnull (NHAN_BAN_GIAO,0) 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  
	SET CUSTOMER_ACT = B.SO_MOI
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'    AS A, 
	SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO
')

print(' --- 2.6 --- Cập nhật lại các dòng NULL ')
exec('
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_UPL_LOAN_TARGET   = ''0'' WHERE AVGBAL_UPL_LOAN_TARGET   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_LOAN_TARGET  = ''0'' WHERE AVGBAL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_DEPOSIT_TARGET  = ''0'' WHERE AVGBAL_DEPOSIT_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NFI_TARGET  = ''0'' WHERE NFI_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NII_TARGET  = ''0'' WHERE NII_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NEWNPL_LOAN_TARGET  = ''0'' WHERE NEWNPL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CREDIT_CARD_TARGET  = ''0'' WHERE CREDIT_CARD_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CUSTOMER_TARGET   = ''0'' WHERE CUSTOMER_TARGET   IS NULL

--- Cập nhật các KPI actual đang bị NULL thành 0 ---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_UPL_LOAN_ACT   = ''0'' WHERE AVGBAL_UPL_LOAN_ACT   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_LOAN_ACT  = ''0'' WHERE AVGBAL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_DEPOSIT_ACT  = ''0'' WHERE AVGBAL_DEPOSIT_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NFI_ACT  = ''0'' WHERE NFI_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NII_ACT  = ''0'' WHERE NII_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NEWNPL_LOAN_ACT  = ''0'' WHERE NEWNPL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CREDIT_CARD_ACT  = ''0'' WHERE CREDIT_CARD_ACT  IS NULL OR CREDIT_CARD_ACT  < 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CUSTOMER_ACT   = ''0'' WHERE CUSTOMER_ACT   IS NULL OR CUSTOMER_ACT < 0
')

print('--- 2.7 --- Tính điểm hoàn thành KPIs: Lấy thực tế chia kế hoạch, Max là 1.5, riêng nợ xấu = 1-(Thực tế - Kế hoạch)/Kế hoạch')
exec(' 
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
SET AVGBAL_UPL_LOAN_REAL = ( CASE WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET END ) ,

	AVGBAL_LOAN_REAL = ( CASE WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET END ) ,

	AVGBAL_DEPOSIT_REAL = ( CASE WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT = 0 THEN 1
	WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT > 0 THEN 1.5
	WHEN AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET END ) ,

	NFI_REAL = ( CASE WHEN NFI_TARGET = 0 AND NFI_ACT = 0 THEN 1
	WHEN NFI_TARGET = 0 AND NFI_ACT > 0 THEN 1.5
	WHEN NFI_TARGET = 0 AND NFI_ACT < 0 THEN 0
	WHEN NFI_ACT / NFI_TARGET < 0 THEN 0 
	WHEN NFI_ACT / NFI_TARGET > 1.5 THEN 1.5 ELSE NFI_ACT / NFI_TARGET END ) ,

	NII_REAL = ( CASE WHEN NII_TARGET = 0 AND NII_ACT = 0 THEN 1
	WHEN NII_TARGET = 0 AND NII_ACT > 0 THEN 1.5
	WHEN NII_TARGET = 0 AND NII_ACT < 0 THEN 0
	WHEN NII_ACT / NII_TARGET < 0 THEN 0
	WHEN NII_ACT / NII_TARGET > 1.5 THEN 1.5 ELSE NII_ACT / NII_TARGET END ) ,

	NEWNPL_LOAN_REAL =(CASE WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT =0 THEN 1 
	WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT > 0 THEN 0
	WHEN NEWNPL_LOAN_TARGET >0 AND NEWNPL_LOAN_ACT = 0 THEN 1.5
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET < 0 THEN 0 
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET >1.5 THEN 1.5 
	ELSE 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET END ),

	CREDIT_CARD_REAL = ( CASE WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT = 0 THEN 1
	WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT > 0 THEN 1.5
	WHEN CREDIT_CARD_ACT / CREDIT_CARD_TARGET > 1.5 THEN 1.5 ELSE CREDIT_CARD_ACT / CREDIT_CARD_TARGET END),

	CUSTOMER_REAL = ( CASE WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT = 0 THEN 1
	WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT > 0 THEN 1.5
	WHEN CUSTOMER_ACT / CUSTOMER_TARGET > 1.5 THEN 1.5 ELSE CUSTOMER_ACT / CUSTOMER_TARGET END  )  
')

print('--- 2.8 --- Tính điểm hoàn thành KPI tổng: lấy từng điểm thành phần nhân trọng số--- ')
exec('
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'     
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.2 +AVGBAL_LOAN_REAL *0.15 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.2 +NEWNPL_LOAN_REAL*0.05 +CREDIT_CARD_REAL*0.05 +CUSTOMER_REAL *0.15 )  
		WHERE DEPT = ''MBO''

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.15 +AVGBAL_LOAN_REAL *0.2 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.25 +NEWNPL_LOAN_REAL*0.05 +CUSTOMER_REAL *0.15)  
		WHERE DEPT = ''SBO''

--- làm tròn điểm tính Incentive ----
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
		SET KPI_ROUND = ROUND (KPI_FINAL*100,0) 
')
--DECLARE @DATE_NOW NVARCHAR(8) =(SELECT MAX(CONVERT(NVARCHAR(8), BUSINESS_DATE,112)) FROM  M74.BICDATA.DBO.BALANCE_LIVE)
--PRINT (@DATE_NOW)
--DECLARE @DATE_NOW_1 NVARCHAR(10) = convert(nvarchar(10),(select convert(date, @DATE_NOW)),120)--(SELECT MAX(CONVERT(NVARCHAR(10), BUSINESS_DATE,120)) FROM  M74.BICDATA.DBO.BALANCE_LIVE)
--PRINT (@DATE_NOW_1)
--DECLARE @STARTOFMONTH NVARCHAR (20)= CONVERT (NVARCHAR(8),(DATEADD(M,DATEDIFF(MONTH, 0, CONVERT(DATE,@DATE_NOW)),0)),112)
--PRINT @STARTOFMONTH
--DECLARE @STARTOFMONTH_1 NVARCHAR (20)= convert(nvarchar(10),(select convert(date, @STARTOFMONTH)),120)
--PRINT @STARTOFMONTH_1
--DECLARE @ENDMONTH_LAST_3 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),120)
--PRINT @ENDMONTH_LAST_3
--DECLARE @ENDMONTH_LAST_3_1 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),112)
--PRINT @ENDMONTH_LAST_3_1
--DECLARE @YEARMONTH_LAST_MONTH VARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(D, -1,@STARTOFMONTH),112)
--PRINT @YEARMONTH_LAST_MONTH

print('--- 3 --- In ra bảng KQ --- ')
exec('
DELETE from SME2017DAILY.DBO.FINAL_MSBO_KQ where BUSINESS_DATE = '''+@DATE_NOW+'''

Insert into SME2017DAILY.DBO.FINAL_MSBO_KQ
select '''+@DATE_NOW+''' BUSINESS_DATE
	  ,BRANCH_CODE
	  ,SALE_NAME  
      ,DAO 
      ,POSITION  
	  ,TIME_WORK 
      ,REGION 
      ,STAFF_ID 
      , DEPT
	  ,AVGBAL_LOAN_TARGET 
	  ,AVGBAL_DEPOSIT_TARGET 
	  ,NEWNPL_LOAN_TARGET 
      ,AVGBAL_UPL_LOAN_TARGET  
	  ,CUSTOMER_TARGET 
      ,CREDIT_CARD_TARGET 
      ,AVGBAL_LOAN_ACT  
	  ,AVGBAL_DEPOSIT_ACT  
	  ,NEWNPL_LOAN_ACT 
      ,AVGBAL_UPL_LOAN_ACT 
	  ,CUSTOMER_ACT
      ,CREDIT_CARD_ACT 
      ,AVGBAL_LOAN_REAL 
	  ,AVGBAL_DEPOSIT_REAL 
	  ,NEWNPL_LOAN_REAL 
      ,AVGBAL_UPL_LOAN_REAL  
	  ,CUSTOMER_REAL 
      ,CREDIT_CARD_REAL
      ,KPI_FINAL
      ,KPI_ROUND
	  FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
	  where AVGBAL_UPL_LOAN_TARGET > 0
	  and AVGBAL_LOAN_TARGET > 0
	  and AVGBAL_DEPOSIT_TARGET > 0
	  and sale_name is not null
	  and position is not null
	  and dao collate database_default  not in (N''Không có mã DAO'',N''Không có mã DAO'')
	  and dao collate database_default in (select dao from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+' where contract_status =N''Chính thức'')
	  and dao collate database_default in (select dao from SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE)
	  order by REGION, branch_code, DAO
	
	DELETE from SERVER12.SME_KPIS.DBO.FINAL_MSBO_KQ

	INSERT SERVER12.SME_KPIS.DBO.FINAL_MSBO_KQ
	select * from SME2017DAILY.DBO.FINAL_MSBO_KQ
')

print('--- 4 --- Lấy dữ liệu thô
--- 4.1 DƯ NỢ TÍN CHẤP ')

exec('
DELETE from SME2017DAILY.DBO.RAW_DU_NO_TIN_CHAP_BQ where BUSINESS_DATE = '''+@DATE_NOW+'''

insert into SME2017DAILY.dbo.RAW_DU_NO_TIN_CHAP_BQ
SELECT '''+@DATE_NOW+''' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVG_BAL,PRODUCT_DETAIL, CHUONG_TRINH_SP , ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+') THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE
	From SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE  (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN'' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+') 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in (select CHUONG_TRINH_SP from CHUONG_TRINH_SP_off)
	and CHUONG_TRINH_SP not like ''%BIL%'') or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90) or Channel not in (select channel from channel_off))
	and (CHUONG_TRINH_SP in (select CHUONG_TRINH_SP from CHUONG_TRINH_SP_off) or 
	(Product_name = ''6. Overdraft'' and PRODUCT_DETAIL =''6.3. Unsecured Overdraft'') or CHUONG_TRINH_SP like ''%BIL%'') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE)
	order by branch_code,DAO
')

print('--- 4.2 DƯ NỢ BQ')
exec('
DELETE from SME2017DAILY.DBO.RAW_DU_NO_BQ where BUSINESS_DATE = '''+@DATE_NOW+'''

insert into SME2017DAILY.dbo.RAW_DU_NO_BQ
SELECT  '''+@DATE_NOW+''' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVG_BAL,PRODUCT_DETAIL, CHUONG_TRINH_SP, ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+') THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE
	FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN''
	AND ((channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (select channel from channel_off) or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+')
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+') 
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE)
	order by branch_code,DAO
')

print('--- 4.3 Huy động BQ ')
exec('
DELETE from SME2017DAILY.DBO.RAW_HUY_DONG_BQ where BUSINESS_DATE = '''+@DATE_NOW+'''

Insert into SME2017DAILY.dbo.RAW_HUY_DONG_BQ

SELECT '''+@DATE_NOW+''' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVG_BAL,PRODUCT_DETAIL, ISSUE_DATE , (CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
(SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+') THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE 
	FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''DEPOSIT''
	and DAO is not null
	and (channel <> ''SMETLS'' and channel <> ''PSASME'' or channel is null)  ---Channel CTV 
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE)
	order by branch_code,DAO
')

print('--- 4.4 Số lượng KHHĐ
--- 4.4.1 -- KHHD tăng thêm')
exec('
DELETE from SME2017DAILY.DBO.RAW_KHHD_TANG_THEM where BUSINESS_DATE = '''+@DATE_NOW+'''

Insert INTO SME2017DAILY.dbo.RAW_KHHD_TANG_THEM
SELECT '''+@DATE_NOW+''' BUSINESS_DATE, DAO, SO_LUY_KE, DEC_2016, BAN_GIAO, NHAN_BAN_GIAO, SO_MOI as SO_KHHD_TANG_THEM
	FROM SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
	WHERE  DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE)
	order by DAO

--- Chi tiết KHHĐ
--- Ngày chốt dữ liệu
DELETE from SME2017DAILY.DBO.RAW_KHHD where BUSINESS_DATE = '''+@DATE_NOW+'''

Insert INTO SME2017DAILY.dbo.RAW_KHHD
SELECT '''+@DATE_NOW+''' BUSINESS_DATE ,RECID,CUS_NAME, CUS_OPEN_DATE, DAO, COMPANY_BOOK
	FROM .SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_'+@DATE_NOW+') 
	AND DAO IS NOT NULL
	AND  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in  (select channel from channel_off) or channel is null)
	AND SEGMENT=''SMES''
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE)
	order by COMPANY_BOOK, DAO
')

print('--- 4.5 Nợ xấu')
exec('
DELETE from SME2017DAILY.DBO.RAW_NO_XAU where BUSINESS_DATE = '''+@DATE_NOW+'''

INSERT into SME2017DAILY.dbo.RAW_NO_XAU
SELECT '''+@DATE_NOW+''' BUSINESS_DATE,BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, BAL_QD, PRODUCT_NAME as PRODUCT_DETAIL, CHUONG_TRINH_SP ,ISSUE_DATE
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme where (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+') 
	AND (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (select channel from channel_off) or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+')))
	order by branch_code,DAO
')

print('--- 4.6 Thẻ ')
exec('
---drop table #a
	select ACNT_CONTRACT_ID, ACNT_CONTRACT_OID, CONTRACT_NUMBER, CARD_NUMBER, CARD_STATUS, CARD_DATE_OPEN, PRODUCTION_STATUS,
			CARD_TYPE, UNLOCK_DATE, T24_DAO, CONTRACT_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE into #a from  SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
			where T24_DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+') and (unlock_date >= ''2017-01-01'' 
			 or 	(((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''))

	alter table #a
	add DAO_CIF nvarchar (50)

	update #a
	set DAO_CIF= B.DAO 
	from #a A, SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	where A.CONTRACT_CIF = B.RECID 

	alter table #a
	add BRANCH nvarchar(50)

	DELETE from SME2017DAILY.DBO.RAW_THE where BUSINESS_DATE = '''+@DATE_NOW+'''

	Insert into SME2017DAILY.dbo.RAW_THE
	select '''+@DATE_NOW+''' BUSINESS_DATE, CONTRACT_NUMBER, CARD_NUMBER, CONTRACT_CIF as CIF, CARD_STATUS,CARD_DATE_OPEN, CARD_TYPE,		UNLOCK_DATE, T24_DAO DAO, DAO_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE, CASE WHEN ACTUAL_DATE_EXPIRE is not null then N''Thẻ đóng'' END NOTE
	from #a
')